package com.soft.dao;

import com.soft.entity.Knowledge;
import com.soft.util.DBHelper;
import com.soft.util.PageBean;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//红酒知识
public class KnowledgeDAO {
    public Map<String, Object> getAll(PageBean pb) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Knowledge> list = new ArrayList<>();


        int totalCount = getTotalCount();
        pb.setTotalCount(totalCount);

        if (pb.getCurrentPage() <= 0) {
            pb.setCurrentPage(1);
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            pb.setCurrentPage(pb.getTotalPage());
        }


        int currentPage = pb.getCurrentPage();
        int index = (currentPage - 1) * pb.getPageCount();
        int count = pb.getPageCount();
        try {
            String sql = "select * from knowledge limit ?,?;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setInt(1, index);
            stmt.setInt(2, count);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Knowledge lists = new Knowledge();
                lists.setId(rs.getInt("id"));
                lists.setTitle(rs.getString("title"));
                lists.setAuthor(rs.getString("author"));
                lists.setContent(rs.getString("content"));
                lists.setDate(rs.getString("date"));
                list.add(lists);
            }
            Map<String, Object> map = new HashMap<>();
            map.put("PageDate", pb);
            map.put("list", list);
            return map;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }



    public int getTotalCount() throws Exception {
        PreparedStatement pStatement = null;
        ResultSet rSet = null;
        String sql = "select count(*) from knowledge";
        int count = 0;
        try {
            pStatement = DBHelper.getConnection().prepareStatement(sql);
            rSet = pStatement.executeQuery();
            rSet.next();
            count = rSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (rSet != null) {
                try {
                    rSet.close();
                    rSet = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (pStatement != null) {
                try {
                    pStatement.close();
                    pStatement = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return count;
    }


    public void add(Knowledge knowledge) {
        String sql = "insert into knowledge(title,author,content,date) values(?,?,?,?);";
        PreparedStatement pstmt = null;

        try {
            pstmt = DBHelper.getConnection().prepareStatement(sql);
            pstmt.setString(1, knowledge.getTitle());
            pstmt.setString(2, knowledge.getAuthor());
            pstmt.setString(3, knowledge.getContent());
            pstmt.setString(4, knowledge.getDate());
            pstmt.execute();
        }
        catch (Exception e) {
            throw new RuntimeException(e);
        } finally {

            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }



    public void delete(Knowledge knowledge) {
        String sql = "delete from knowledge where id=?;";
        PreparedStatement pstmt = null;

        try {
            pstmt = DBHelper.getConnection().prepareStatement(sql);
            pstmt.setInt(1, knowledge.getId());
            pstmt.execute();
        }
        catch (Exception e) {
            throw new RuntimeException(e);
        } finally {

            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }




    public Knowledge getone(Knowledge knowledge) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            String sql = "select * from knowledge where id=? ;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setInt(1, knowledge.getId());
            rs = stmt.executeQuery();

            if (rs.next()) {
                Knowledge lists = new Knowledge();
                lists.setId(rs.getInt("id"));
                lists.setTitle(rs.getString("title"));
                lists.setAuthor(rs.getString("author"));
                lists.setContent(rs.getString("content"));
                lists.setDate(rs.getString("date"));
                return lists;
            }
            return null;
        }
        catch (Exception ex) {
            ex.printStackTrace();
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return null;
    }




    public Map<String, Object> search(PageBean pb, Knowledge knowledge) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Knowledge> list = new ArrayList<>();


        int totalCount = getTotalCount();
        pb.setTotalCount(totalCount);


        if (pb.getCurrentPage() <= 0) {
            pb.setCurrentPage(1);
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            pb.setCurrentPage(pb.getTotalPage());
        }

        int currentPage = pb.getCurrentPage();
        int index = (currentPage - 1) * pb.getPageCount();
        int count = pb.getPageCount();

        try {
            String sql = "select * from knowledge where title like concat('%',?,'%') limit ?,?;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setString(1, knowledge.getTitle());
            stmt.setInt(2, index);
            stmt.setInt(3, count);
            rs = stmt.executeQuery();

            while (rs.next()) {
                Knowledge lists = new Knowledge();
                lists.setId(rs.getInt("id"));
                lists.setTitle(rs.getString("title"));
                lists.setAuthor(rs.getString("author"));
                lists.setContent(rs.getString("content"));
                lists.setDate(rs.getString("date"));
                list.add(lists);
            }
            Map<String, Object> map = new HashMap<>();
            map.put("PageDate", pb);
            map.put("list", list);
            return map;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return null;
    }



    public void update(Knowledge knowledge) {
        String sql = "update knowledge set title=?,author=?,content=?,date=? where id=? ;";
        PreparedStatement pstmt = null;

        try {
            pstmt = DBHelper.getConnection().prepareStatement(sql);
            pstmt.setString(1, knowledge.getTitle());
            pstmt.setString(2, knowledge.getAuthor());
            pstmt.setString(3, knowledge.getContent());
            pstmt.setString(4, knowledge.getDate());
            pstmt.setInt(5, knowledge.getId());
            pstmt.execute();
        }
        catch (Exception e) {
            throw new RuntimeException(e);
        } finally {

            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
